﻿' Copyright © Microsoft Corporation.  All Rights Reserved.
' This code released under the terms of the 
' Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

Public Class ThisWorkbook

    ''' <summary>
    ''' The DataGridView control on ActionsPane and the ListObject control 
    ''' on Sheet1 share the same BindingSource. When value in DataGridView
    ''' changes, the ListObject value will change accordingly. However,
    ''' since Sheet1 is protected, unprotecting Sheet1 is needed for ListObject
    ''' to change value.
    ''' </summary>
    Friend Class CustomerBindingSource
        Inherits BindingSource

        Protected Overrides Sub OnListChanged(ByVal e As System.ComponentModel.ListChangedEventArgs)
            Try
                Try
                    ' Unprotects Sheet1
                    Globals.Sheet1.UnprotectSheet()
                    MyBase.OnListChanged(e)
                Finally
                    ' Protects Sheet1
                    Globals.Sheet1.ProtectSheet()
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message, _
                        "Error protecting or unprotecting sheet", _
                        MessageBoxButtons.OK, _
                        MessageBoxIcon.Error, _
                        MessageBoxDefaultButton.Button1)
            End Try

        End Sub

    End Class

    ''' <summary>
    ''' User control which will be added to Actions Pane.
    ''' </summary>
    Private techUserControl As TechniqueUserControl

    ''' <summary>
    ''' A data set for storing data from ExcelSampleData xml file.
    ''' </summary>
    Friend customerDataSet As DataSet = Nothing

    ''' <summary>
    ''' A binding source which is used for data binding to data set.
    ''' </summary>
    Friend custBindingSource As CustomerBindingSource = Nothing

#Region "Methods"
    ''' <summary>
    ''' Loads DataSet with data from ExcelSampleData xml file.
    ''' </summary>
    Private Sub LoadDataSet()
        Try
            If (customerDataSet Is Nothing) Then
                customerDataSet = New DataSet()
            End If
            ' Gets schema file location
            Dim schemaFileLocation As String = System.IO.Path.Combine(Path, "ExcelSampleData.xsd")
            ' Gets xml file location
            Dim xmlFileLocation As String = System.IO.Path.Combine(Path, "ExcelSampleData.xml")

            ' Reads data from schema and xml file
            customerDataSet.ReadXmlSchema(schemaFileLocation)
            customerDataSet.ReadXml(xmlFileLocation)
        Catch ex As Exception
            MessageBox.Show(ex.Message, _
                        "Error loading data set.", _
                        MessageBoxButtons.OK, _
                        MessageBoxIcon.Error, _
                        MessageBoxDefaultButton.Button1)
        End Try
    End Sub

#End Region

    ''' <summary>
    ''' Handles the Startup event for the workbook. When the event fires, 
    ''' LoadDataSet method will be called to load data from XML file into
    ''' customerDataSet, set customerBindingSource's DataSource property  
    ''' to customerDataSet and its DataMember to "Customers". A TechniqueUserControl
    ''' Should be created and attached to the ActionsPane.
    ''' </summary>
    ''' <param name="sender">Unused.</param>
    ''' <param name="e">Unused.</param>
    ''' <remarks></remarks>
    Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        ' Loads data set from xml file
        LoadDataSet()

        ' Creates BindingSource
        If (custBindingSource Is Nothing) Then
            custBindingSource = New CustomerBindingSource()
        End If

        custBindingSource.DataSource = customerDataSet
        custBindingSource.DataMember = "Customer"

        ' Adding rows in the data grid will not unprotect the sheet when the 
        ' ListObject tries to change size, which will cause an exception. Set the
        ' AllowNew property to false to force the data set to stay the same size.
        custBindingSource.AllowNew = False

        ' Adds user control to Actions Pane.
        techUserControl = New TechniqueUserControl()
        ActionsPane.Controls.Add(techUserControl)
    End Sub

    Private Sub ThisWorkbook_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

    End Sub

End Class
